﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using eCommerce.core;
using eCommerce.db.BORespository;
using eCommerce.db.DataRespository;
using eCommerce.db.SQLDataAccess;

namespace eCommerce.db.DALRespository
{
    public class ArticleDal
    {
        #region ArticleDal private
        private static void SetInfo(IDataRecord reader, ref ArticleEntity o)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                PropertyInfo propertyInfo = DbCommon.GetPropertyInfo(o, reader.GetName(i));
                if (((propertyInfo != null) && (reader[i] != null)) && (reader[i] != DBNull.Value))
                    propertyInfo.SetValue(o, reader[i], null);
                else
                    o[reader.GetName(i)] = reader[i];
            }
        }

        private static void SetInfoList(ref SqlDataReader reader, ref List<ArticleEntity> articleList)
        {
            while (reader.Read())
            {
                var o = new ArticleEntity();
                SetInfo(reader, ref o);
                articleList.Add(o);
            }
            reader.Dispose();
        }
        #endregion  

        public static List<ArticleEntity> GetListPagination(int categoryId, int pageIndex, int pageSize, ref int totalRecords)
        {
            var lstCategoryId = string.Empty;
            if (categoryId > 0)
            {
                // Khi nhấn vào một CategoryId cụ thể
                var o = CategoryBo.GetById(categoryId);
                if (o != null && o.ParentId == (int)CategoryType.Root)
                {
                    var lstId = string.Empty;
                    var lstCategory = CategoryBo.GetListByParentId((int)CategoryType.Article, categoryId, (int)CategoryType.Active);

                    if (lstCategory.Count > 0)
                        foreach (CategoryEntity entity in lstCategory)
                            lstId = lstId + string.Format("{0}, ", entity.CategoryId);    

                    if (lstId.Length > 0)
                        lstId = lstId.Substring(0, lstId.Length - 2);

                    lstCategoryId = lstId;
                }
                else
                    lstCategoryId = categoryId.ToString();
            }
            else
            {
                // All khi nhấn vào Tin tức
                var lstParentCategory = CategoryBo.GetListByParentId((int) CategoryType.Article, (int) (CategoryType.Root), (int) (CategoryType.Active));
                if (lstParentCategory.Count > 0)
                    foreach (var parentCategory in lstParentCategory)
                    {
                        var lstId = string.Empty;
                        var lstCategory = CategoryBo.GetListByParentId((int)CategoryType.Article, parentCategory.CategoryId, (int)CategoryType.Active);

                        if (lstCategory.Count > 0)
                            foreach (CategoryEntity entity in lstCategory)
                                lstId = lstId + string.Format("{0}, ", entity.CategoryId);

                        if (lstId.Length > 0)
                            lstCategoryId += lstId;
                        else
                            lstCategoryId += categoryId.ToString();
                    }

                if (lstCategoryId.Length > 0)
                    lstCategoryId = lstCategoryId.Substring(0, lstCategoryId.Length - 2);
            }

            totalRecords = 0;
            var lstArticel = new List<ArticleEntity>();

            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var con = new SqlConnection(DbCommon.ConnectionString);
            try
            {
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    var cmd = new SqlCommand();
                    DbCommon.AddParameter(cmd, "LstCategoryId", string.IsNullOrEmpty(lstCategoryId) ? "0" : lstCategoryId);                    
                    DbCommon.AddParameter(cmd, "Status", (int) ArticleStatus.Focus + (int) ArticleStatus.Published);
                    DbCommon.AddParameter(cmd, "PageIndex", pageIndex);
                    DbCommon.AddParameter(cmd, "PageSize", pageSize);
                    //DbCommon.AddParameter(cmd, "recct", totalRecords);
                    //DbCommon.SetParameterOut(cmd, "recct");

                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = DbCommon.DatabaseSchema + "Article_GetListPagination";

                    var reader = cmd.ExecuteReader();
                    SetInfoList(ref reader, ref lstArticel);
                    reader.Close();

                    return lstArticel;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Article_GetListPagination: {0}", ex.Message));
            }
            return null;
        }

        public static List<ArticleEntity> Search(string keyword, int categoryId, int status, int orderBy, int pageIndex, int pageSize, ref int totalRecords)
        {
            var filter = "1=1";

            if (!string.IsNullOrEmpty(keyword))
                filter += string.Format(" AND (Title LIKE '%{0}%' OR Sapo LIKE '%{0}%')", keyword);

            if (categoryId > 0)
            {
                var o = CategoryBo.GetById(categoryId);
                if (o != null && o.ParentId == (int)CategoryType.Root)
                {
                    var lstId = string.Empty;
                    var lstCategory = CategoryBo.GetListByParentId((int)CategoryType.Article, categoryId, (int)CategoryType.All);
                    lstId = lstCategory.Aggregate(lstId, (current, category) => current + string.Format("{0}, ", category.CategoryId));

                    if (lstId.Length > 0)
                        lstId = lstId.Substring(0, lstId.Length - 2);

                    filter += string.Format(" AND CategoryId IN ({0})", lstId);
                }
                else
                    filter += string.Format(" AND CategoryId = {0}", categoryId);
            }

            if (status > 0)
                filter += string.Format(" AND ([Status] = {0})", status);                              

            var sortBy = orderBy == 2 ? "PublishOn ASC" : "PublishOn DESC";

            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var lstArticle = new List<ArticleEntity>();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Action",
                                Constant.TableArticle, sortBy, Constant.ArticleFieldList, filter, pageIndex, pageSize);

                SetInfoList(ref reader, ref lstArticle);

                totalRecords = (int)SqlHelper.ExecuteScalar(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Action_Count",
                                        Constant.TableArticle, filter);

                return lstArticle;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Article_Action_Pagination: {0}", ex.Message));
            }
        }      

        public static List<ArticleEntity> GetFocusArticle(int count)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var lstArticle = new List<ArticleEntity>();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Action",
                                Constant.TableArticle, "PublishOn DESC", Constant.ArticleFieldList, "[Status] = 4", Constant.PageIndex, count);

                SetInfoList(ref reader, ref lstArticle);

                return lstArticle;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Article_Action_Focus_Article: {0}", ex.Message));
            }
        }

        public static ArticleEntity GetById(int id)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var article = new ArticleEntity();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Article_GetById", id);
                if (reader.Read())
                    SetInfo(reader, ref article);
                return article;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Article_GetById: {0}", ex.Message));
            }
        }

        public static List<ArticleEntity> GetMostView(int top)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var lstArticle = new List<ArticleEntity>();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Article_GetMostView", top,(int) ArticleStatus.Focus);
                if (reader.Read())
                    SetInfoList(ref reader, ref lstArticle);
                return lstArticle;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Article_GetMostView: {0}", ex.Message));
            }
        }

        public static ArticleEntity GetByCatId(int catid)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var article = new ArticleEntity();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Article_GetByCatId", catid);
                if (reader.Read())
                    SetInfo(reader, ref article);
                return article;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Article_GetById: {0}", ex.Message));
            }
        }

        public static void Save(ArticleEntity o)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            try
            {
                if (o.ArticleId > 0)
                {
                    SqlHelper.ExecuteNonQuery(
                    DbCommon.ConnectionString,
                    DbCommon.DatabaseSchema + "Article_Update",
                    o.ArticleId, o.CategoryId, o.Title, o.Url, o.Sapo, o.Body, o.Source, o.ImgAvatar, o.EditedBy, o.ApprovedBy, o.PublishOn, o.Status);
                }
                else
                {
                    SqlHelper.ExecuteNonQuery(
                        DbCommon.ConnectionString,
                        DbCommon.DatabaseSchema + "Article_Create",
                        o.CategoryId, o.Title, o.Url, o.Sapo, o.Body, o.Source, o.ImgAvatar, o.PublishOn, o.CreatedBy, o.Status);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Article Save: {0}", ex.Message));
            }
        }

        public static void ChangeStatus(int approvedBy, int articleId, int status)
        {
            try
            {                
                SqlHelper.ExecuteNonQuery(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Article_Update_Status", approvedBy, articleId, status);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Article_Update_Status: {0}", ex.Message));
            }
        }

        public static void Delete(int id)
        {
            try
            {
                if (id > 0)
                    SqlHelper.ExecuteNonQuery(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Article_Delete", id);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Article_Delete: {0}", ex.Message));
            }
        }
    }
}
